package com.zhenhua.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.common.dao.BaseDao;
import com.common.entity.Appointment;
import com.zekai.dao.impl.ExamDetailDaoImpl;
import com.zekai.dao.impl.ExamTypeDaoImpl;
import com.zhenhua.dao.IAppointmentDao;

import utils.Page;

public class AppointmentDaoImpl extends BaseDao implements IAppointmentDao {

	@Override
	public int insert(Appointment appointment) {
		int result=0;
		String sql="INSERT INTO appoint(stuNumber,coachNumber,appDate,recordDare,"
				+ "magNumber,sub_number,sub_detail_num,remark) VALUES(?,?,?,?,?,?,?,?)";
		Object[] objects=new Object[]{appointment.getStuNumber(),appointment.getCoachNumber(),appointment.getAppDate(),
				appointment.getRecordDate(),appointment.getMagNumber(),appointment.getSubNumber(),
				appointment.getSubDetailNumber(),appointment.getRemarks()};
		result=excuteUpdate(sql, objects);
		return result;
	}

	@Override
	public int deleteById(int id) {
        String sql="DELETE FROM appoint WHERE id=?";
		return excuteUpdate(sql, id);
	}

	@Override
	public int update(Appointment appointment) {
		int result=0;
		String sql="UPDATE appoint SET stuNumber=?,coachNumber=?,appDate=?,recordDare=?,"
				+ "magNumber=?,sub_number=?,sub_detail_num=?,remark=? WHERE id=?";
		Object[] objects=new Object[]{appointment.getStuNumber(),appointment.getCoachNumber(),appointment.getAppDate(),
				appointment.getRecordDate(),appointment.getMagNumber(),appointment.getSubNumber(),
				appointment.getSubDetailNumber(),appointment.getRemarks(),appointment.getAppointId()};
		result=excuteUpdate(sql, objects);
		return result;
	}

	@Override
	public List<Appointment> getAppointmentByStuNumber(int stuNumber) {
		
		String sql="SELECT * FROM appoint WHERE stuNumber=?";
		List<Appointment> appointments=extuteQuery(sql, stuNumber);
				
		return appointments;
	}
	

	public List<String> getSubName(String date, int stuId) {
		String sql="select sub_number,sub_detail_num from appoint where stuNumber=? and appDate like ?";
		List<String> list=new ArrayList<>();
		Connection connection=null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		try {
			connection=getConnection();
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setObject(1, ""+stuId);
			preparedStatement.setObject(2, "%"+date+"%");
			resultSet=preparedStatement.executeQuery();
			if (resultSet.next()) {
				ExamTypeDaoImpl examTypeDaoImpl=new ExamTypeDaoImpl();
				String subName=examTypeDaoImpl.getNameById(resultSet.getInt("sub_number"));
				list.add(subName);
				ExamDetailDaoImpl examDetailDaoImpl=new ExamDetailDaoImpl();
				String detailName=examDetailDaoImpl.getNameById(resultSet.getInt("sub_detail_num"));
				list.add(detailName);
			}else {
				return null;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Appointment> listall() {
		List<Appointment> appointments=new ArrayList<Appointment>();
		String sql="SELECT id,stuNumber,coachNumber,appDate,recordDare,magNumber,sub_number,sub_detail_num,remark FROM appoint WHERE 1=? ";
		appointments=extuteQuery(sql, 1);	
		return appointments;
	}

	
	


	@Override
	public int getCountByStuNumber(Integer stuNumber) {
		
		List<Appointment> appointments=new ArrayList<Appointment>();
		if(stuNumber!=null){
		    String sql="SELECT * FROM appoint WHERE stuNumber= ?";
		    appointments=extuteQuery(sql, stuNumber);
		}else {
			String sql="SELECT * FROM appoint WHERE 1=?";
			appointments=extuteQuery(sql, 1);
		}
		return appointments.size();
	}
	@Override
	public List<Appointment> getOnePageInfoByStuNumber(Integer stuNumber, int currentPage, int pageSize) {
		List<Appointment> appointments=new ArrayList<Appointment>();
		if(stuNumber!=null){
		    String sql="SELECT * FROM appoint WHERE stuNumber= ? LIMIT ? , ?";
		    Object[] objects={stuNumber,(currentPage-1)*pageSize,pageSize};
		    appointments=extuteQuery(sql, objects);
		}else {
			String sql="SELECT * FROM appoint WHERE 1=? LIMIT ? , ?";
			Object[] objects={1,(currentPage-1)*pageSize,pageSize};
			appointments=extuteQuery(sql, objects);
		}
		return appointments;
	}
	@Override
	public Page<Appointment> getPageByStuNumber(Integer stuNumber, int currentPage, int pageSize) {
		
		int count=this.getCountByStuNumber(stuNumber);
		List<Appointment> list=this.getOnePageInfoByStuNumber(stuNumber, currentPage, pageSize);
		Page<Appointment> page=new Page<Appointment>(currentPage, count, pageSize, list);
		return page;
		
	}
	@Override
	public int getCountByCoachNumber(Integer coachNumber) {
		List<Appointment> appointments=new ArrayList<Appointment>();
		if(coachNumber!=null){
		    String sql="SELECT * FROM appoint WHERE coachNumber= ?";
		    appointments=extuteQuery(sql, coachNumber);
		}else {
			String sql="SELECT * FROM appoint WHERE 1=?";
			appointments=extuteQuery(sql, 1);
		}
		return appointments.size();
	}
	@Override
	public List<Appointment> getOnePageInfoByCoachNumber(Integer coachNumber, int currentPage, int pageSize) {
		List<Appointment> appointments=new ArrayList<Appointment>();
		if(coachNumber!=null){
		    String sql="SELECT * FROM appoint WHERE coachNumber= ? LIMIT ? , ?";
		    Object[] objects={coachNumber,(currentPage-1)*pageSize,pageSize};
		    appointments=extuteQuery(sql, objects);
		}else {
			String sql="SELECT * FROM appoint WHERE 1=? LIMIT ? , ?";
			Object[] objects={1,(currentPage-1)*pageSize,pageSize};
			appointments=extuteQuery(sql, objects);
		}
		return appointments;
	}
	@Override
	public Page<Appointment> getPageByCoachNumber(Integer coachNumber, int currentPage, int pageSize) {
		
		int count=this.getCountByCoachNumber(coachNumber);
		List<Appointment> list=this.getOnePageInfoByCoachNumber(coachNumber, currentPage, pageSize);
		Page<Appointment> page=new Page<Appointment>(currentPage, count, pageSize, list);
		return page;
	}
	
	
	@Override
    public Object getEntity(ResultSet rs) {
		Appointment appointment=new Appointment();
		try {
			appointment.setAppointId(rs.getInt("id"));
			appointment.setStuNumber(rs.getInt("stuNumber"));
			appointment.setCoachNumber(rs.getInt("coachNumber"));
			try {
				appointment.setAppDate(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(rs.getString("appDate")));
				appointment.setRecordDate(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(rs.getString("recordDare")));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			appointment.setMagNumber(rs.getInt("magNumber"));
			appointment.setSubNumber(rs.getInt("sub_number"));
			appointment.setSubDetailNumber(rs.getInt("sub_detail_num"));
			appointment.setRemarks(rs.getString("remark"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return appointment;
	}

	@Override
	public Appointment getAppointmentById(int id) {
		String sql="SELECT * FROM appoint WHERE id=?";
		List<Appointment> appointments=extuteQuery(sql, id);
		return appointments.get(0);
	}

	

//	public List<Appointment> testDate(int id){
//		String sql="select * from appoint where stuNumber=?";
//		Connection connection=null;
//		PreparedStatement preparedStatement=null;
//		ResultSet rs=null;
//		List<Appointment> list=new ArrayList<>();
//		try {
//			connection=getConnection();
//			preparedStatement=connection.prepareStatement(sql);
//			preparedStatement.setObject(1, id);
//			rs=preparedStatement.executeQuery();
//			if (rs.next()) {
//				Appointment appointment=new Appointment();
//				appointment.setAppointId(rs.getInt("id"));
//				appointment.setStuNumber(rs.getInt("stuNumber"));
//				appointment.setCoachNumber(rs.getInt("coachNumber"));
//				
//				
//				appointment.setMagNumber(rs.getInt("magNumber"));
//				appointment.setSubNumber(rs.getInt("sub_number"));
//				appointment.setSubDetailNumber(rs.getInt("sub_detail_num"));
//				appointment.setRemarks(rs.getString("remark"));
//				list.add(appointment);
//			}else {
//				return null;
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		return list;
//	}

	

}
